/**
 * 
 */
package com.flux.tms.tm${bean.numMain}web.modules.t${bean.numMain}${bean.numSub}.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.stereotype.Repository;
import com.flux.scev6.function.functionhelper.SFHelper;
import com.flux.tms.tm${bean.numMain}web.modules.t${bean.numMain}${bean.numSub}.${bean.javaFlieName}CVal;
import com.flux.scev6.dbopr.BaseDao;
import com.flux.scev6.dbopr.QueryHelper;
import com.flux.scev6.dbopr.query.pagequery.PageQueryDataSet;
import com.flux.scev6.login.model.LoginInfoObj;
import com.flux.scev6.udfquery.model.QrySqlObj;
import com.flux.scev6.udfquery.model.QueryCondition;

/**
 *
 * T${bean.numMain}${bean.numSub} ${bean.notesMain} -- ${bean.notesSub}    -- web  DAO 定义
 * 
 * T${bean.numMain}${bean.numSub} ${bean.notesSub}
 * 
 * <p>
 * 创建时间：${bean.dayTime}
 * <p>
 * 
 * <p>
 * 修改时间：${bean.dayTime}
 * <p>
 * 
 * @author ${bean.author}
 * 
 * @version V1.0.0
 *
 */
@Repository
public class ${bean.javaFlieName}Dao extends BaseDao {
	
	/*
	 * V100(${bean.numTime}) ${bean.author} 初版。
	 */
	@SuppressWarnings("unused")
	private static String verNum = "V100(${bean.numTime})";// 版本号
	
	/**
	 * 查询符合输入的查询条件的 ${bean.numSub}--主表 列表记录 (分页查询)
	 *
	 * @author ${bean.author}
	 *
	 * @date ${bean.dayTime}
	 * 
	 * @param loginInfoObj
	 * @param conditionMap
	 * @param otherReturnFields
	 * @return
	 */
	public PageQueryDataSet get${bean.javaFlieName}sListByPage(LoginInfoObj loginInfoObj, Map<String, String> conditionMap,
			String otherReturnFields) {
		// ==================================================================================
		// ==================================================================================
		// ==================================================================================
		PageQueryDataSet pageQueryDataSet = get${bean.javaFlieName}sList(loginInfoObj, conditionMap, otherReturnFields, true);
		return pageQueryDataSet;
		// ==================================================================================
		// ==================================================================================
		// ==================================================================================
	}

	/**
	 * 查询符合输入的查询条件的 ${bean.numSub}--主表 列表记录 (分页查询)
	 *
	 * @author ${bean.author}
	 *
	 * @date ${bean.dayTime}
	 * 
	 * @param loginInfoObj
	 * @param conditionMap
	 * @param otherReturnFields
	 * @param isPageQuery
	 * @return
	 */
	public PageQueryDataSet get${bean.javaFlieName}sList(LoginInfoObj loginInfoObj, Map<String, String> conditionMap,
			String otherReturnFields, boolean isPageQuery) {
		// ==================================================================================
		// ==================================================================================
		// ==================================================================================
		PageQueryDataSet pageQueryDataSet = null;
		// ==============================================================================
		// ==============================================================================
		// ==============================================================================
		QrySqlObj qrySqlObj = getQuerySql(loginInfoObj, conditionMap, otherReturnFields, true);
		// ==============================================================================
		// ==============================================================================
		// ==============================================================================
		// 执行数据库查询
		
	
		if (isPageQuery) {
			// 分页查询
			QueryCondition pageCondition = new QueryCondition();
			pageCondition.setOrderByList("h1.organizationId,h1.${bean.pk02}");
			pageCondition.setMainKey("h1.organizationId,h1.${bean.pk02}");
			// 执行数据库查询
			pageQueryDataSet = queryForListByPage(loginInfoObj, qrySqlObj.getFullySql(), qrySqlObj.getParamObjectsList(), pageCondition);
			return pageQueryDataSet;
		} else {
			// 非分页查询
			List<Map<String, Object>> rsData = null;
			try {
				rsData = queryForList(loginInfoObj, qrySqlObj.getFullySql(), qrySqlObj.getParamObjectsList());
			} catch (EmptyResultDataAccessException err1) {
				// 执行成功，但是没有对应的记录。
				rsData = new ArrayList<Map<String, Object>>();
			} // 其它异常，比如语法异常等，抛给上层处理。
			pageQueryDataSet = new PageQueryDataSet();
			pageQueryDataSet.setBizDataList(rsData);
			return pageQueryDataSet;
		}
		// ==================================================================================
		// ==================================================================================
		// ==================================================================================
	}
	
	/**
	 * 获取查询语句配置
	 *
	 * @author ${bean.author}
	 *
	 * @date ${bean.dayTime}
	 * 
	 * @param loginInfoObj
	 * @param conditionMap
	 * @param otherReturnFields
	 * @param isQurey
	 * @return
	 */
    public QrySqlObj getQuerySql(LoginInfoObj loginInfoObj, Map<String, String> conditionMap, String otherReturnFields,
    		boolean isQurey){
        // ==================================================================================
        // ==================================================================================
        // ==================================================================================
     
    	// 组织SQL语句和查询条件
		List<Object> paramObjectsList = new ArrayList<Object>();

		StringBuffer sqlBuff = new StringBuffer(50);
		sqlBuff.append("SELECT h1.organizationId<#if (bean.systemCodeList)?size !=0><#assign nameArr=['s1','s2','s3','s4','s5','s6','s7','s8','s9','s10','s11','s12','s13','s14','s15','s16','s17','s18','s19','s20','s21','s22','s23','s24','s25']><#list 0..((bean.systemCodeList)?size-1) as i><#if (bean.tableListStr)?contains((bean.systemCodeList)[i].codeId)>,${nameArr[i]}.codeDescr as ${(bean.systemCodeList)[i].codeId}Name</#if></#list></#if>");
<#if (bean.otherCodeList)?size !=0>
	<#assign nameArr2=['s30','s31','s32','s33','s34','s35','s36','s37','s38','s39','s40','s41','s42','s43','s44']>
	<#list 0..((bean.otherCodeList)?size-1) as k>
		<#if k==(bean.otherCodeList)?size-1>
			sqlBuff.append("${nameArr2[k]}.${((bean.otherCodeList)[k].tableDesc)} as ${((bean.otherCodeList)[k].codeId)}Name");
		<#else>
			sqlBuff.append("${nameArr2[k]}.${((bean.otherCodeList)[k].tableDesc)} as ${((bean.otherCodeList)[k].codeId)}Name,");
		</#if>
		</#list>
</#if>
		if (otherReturnFields != null && otherReturnFields.trim().length() > 0) {
			sqlBuff.append(",");
			sqlBuff.append(otherReturnFields);
		}
		sqlBuff.append(" FROM ").append(${bean.javaFlieName}CVal.${bean.tableName}).append(" h1 ");

	<#if (bean.systemCodeList)?size !=0>
	<#assign nameArr=['s1','s2','s3','s4','s5','s6','s7','s8','s9','s10','s11','s12','s13','s14','s15','s16','s17','s18','s19','s20','s21','s22','s23','s24','s25']>
	<#list 0..((bean.systemCodeList)?size-1) as i>

		//获取系统代码 ${(bean.systemCodeList)[i].codeType}
		sqlBuff.append("LEFT JOIN ").append(${bean.javaFlieName}CVal.TB_BSM_CODE_ML).append(" ${nameArr[i]} ");
		sqlBuff.append("ON ${nameArr[i]}.organizationId=h1.organizationId AND ${nameArr[i]}.codeId = h1.${(bean.systemCodeList)[i].codeId} AND ${nameArr[i]}.languageId = ? AND ${nameArr[i]}.codeType = '${(bean.systemCodeList)[i].codeType}'  ");
		paramObjectsList.add(loginInfoObj.getLanguage());

	</#list>
	</#if>
	<#if (bean.otherCodeList)?size !=0>
		<#assign nameArr2=['s30','s31','s32','s33','s34','s35','s36','s37','s38','s39','s40','s41','s42','s43','s44']>
		<#list 0..((bean.otherCodeList)?size-1) as k>
		//获取关联表 ${((bean.otherCodeList)[k].tableName)}
		sqlBuff.append("LEFT JOIN ").append(${bean.javaFlieName}CVal.TB_${((bean.otherCodeList)[k].tableName)?upper_case}).append(" ${nameArr2[k]} ");
		sqlBuff.append("ON ${nameArr2[k]}.organizationId=h1.organizationId AND ${nameArr2[k]}.${((bean.otherCodeList)[k].tableId)} = h1.${((bean.otherCodeList)[k].codeId)} ");
		</#list>
	</#if>
		sqlBuff.append("WHERE 1 = 1 ");
		sqlBuff.append("AND h1.organizationId = ? "); // 注意：organizationId
														// 是必需的查询条件
		paramObjectsList.add(loginInfoObj.getBizOrgId());// 当前用户登录的企业组织编号
		//查询
		sqlBuff.append(getSearchCondition(conditionMap, paramObjectsList,${bean.javaFlieName}CVal.KEYNAME_${(bean.pk01)?upper_case}));
		sqlBuff.append(getSearchCondition(conditionMap, paramObjectsList,${bean.javaFlieName}CVal.KEYNAME_${(bean.pk02)?upper_case}));
	<#if (bean.pk03)!="">
		sqlBuff.append(getSearchCondition(conditionMap, paramObjectsList,${bean.javaFlieName}CVal.KEYNAME_${(bean.pk03)?upper_case}));
	</#if>
	<#if (bean.pk04)!="">
		sqlBuff.append(getSearchCondition(conditionMap, paramObjectsList,${bean.javaFlieName}CVal.KEYNAME_${(bean.pk04)?upper_case}));
	</#if>
	<#if (bean.pk05)!="">
		sqlBuff.append(getSearchCondition(conditionMap, paramObjectsList,${bean.javaFlieName}CVal.KEYNAME_${(bean.pk05)?upper_case}));
	</#if>
	<#if (bean.searchPk01)?size !=0>
	<#list (bean.searchPk01) as searchBean>
		<#if (searchBean.isId)>
		sqlBuff.append(getSearchCondition(conditionMap, paramObjectsList,${bean.javaFlieName}CVal.KEYNAME_${(searchBean.name)?upper_case}));
		</#if>
	</#list>
	</#if>
		//desc 强制模糊查询
	<#if (bean.searchPk01)?size !=0>
		<#list (bean.searchPk01) as searchBean>
			<#if !(searchBean.isId)>
		sqlBuff.append(getVagueCondition(conditionMap, paramObjectsList,${bean.javaFlieName}CVal.KEYNAME_${(searchBean.name)?upper_case}));
			</#if>
		</#list>
	</#if>
	    //时间段
		//sqlBuff.append(getFromToSql(loginInfoObj, conditionMap, paramObjectsList,"addTimeFrom","addTimeTo",${bean.javaFlieName}CVal.KEYNAME_ADDTIME));

			// 解析查询SQL
		//System.out.println(sqlBuff.toString());
        QrySqlObj qrySqlObj = sqlParserService.processQuerySelect(loginInfoObj, sqlBuff.toString(), paramObjectsList, conditionMap, isQurey);

        return qrySqlObj;
    }


	/**
	 * 查询符合输入的查询条件的 ${bean.numSub}--主表 列表记录
	 *
	 * @author ${bean.author}
	 *
	 * @date ${bean.dayTime}
	 * 
	 * @param loginInfoObj
	 * @param conditionMap
	 * @param otherReturnFields
	 * @return
	 */
	public List<Map<String, Object>> get${bean.javaFlieName}sList(LoginInfoObj loginInfoObj, Map<String, String> conditionMap,
			String otherReturnFields) {
		// ==================================================================================
		// ==================================================================================
		// ==================================================================================
		PageQueryDataSet pageQueryDataSet = get${bean.javaFlieName}sList(loginInfoObj, conditionMap, otherReturnFields, false);
		return pageQueryDataSet.getBizDataList();
		// ==================================================================================
		// ==================================================================================
		// ==================================================================================
	}
	
	
	/**
	 * 获取查询语句 id
	 *
	 * @author ${bean.author}
	 *
	 * @date ${bean.dayTime}
	 * 
	 * @param conditionMap
	 * @param paramObjectsList
	 * @param keyId
	 * @return
	 */
	private String getSearchCondition(Map<String, String> conditionMap,List<Object> paramObjectsList,String keyId){
		String keyValue = conditionMap.get(keyId);
		StringBuffer sqlBuff = new StringBuffer();
		if (SFHelper.isNotNull(keyValue)) {
			keyValue = keyValue.trim();
			if (keyValue.startsWith("%") || keyValue.endsWith("%")) {
				sqlBuff.append("AND h1.").append(keyId).append(" like ? ");
			} else {
				sqlBuff.append("AND h1.").append(keyId).append(" = ? ");
			}
			paramObjectsList.add(keyValue.toUpperCase());
		}
		return sqlBuff.toString();
	}
	
	/**
	 * 获取查询语句 desc
	 *
	 * @author ${bean.author}
	 *
	 * @date ${bean.dayTime}
	 * 
	 * @param conditionMap
	 * @param paramObjectsList
	 * @param keyId
	 * @return
	 */
	public String getVagueCondition(Map<String, String> conditionMap,List<Object> paramObjectsList,String keyId){
		    StringBuffer sqlBuff = new StringBuffer();
	        String keyValue = conditionMap.get(keyId);
	        if (keyValue != null && keyValue.trim().length() > 0) {
	            keyValue = keyValue.trim();
	            if (keyValue.startsWith("%") || keyValue.endsWith("%")) {
	                sqlBuff.append("AND h1.").append(keyId).append(" like ? ");
	                paramObjectsList.add(keyValue.trim().toUpperCase());
	            } else {
	                sqlBuff.append("AND h1.").append(keyId).append(" like ? ");
	                paramObjectsList.add("%"+keyValue.trim().toUpperCase()+"%");
	            }
	        }
	        return sqlBuff.toString();
	}

	/**
	* 获取多条件查询语句
	*
	* @author ${bean.author}
	*
	* @date ${bean.dayTime}
	*
	* @param conditionMap
	* @param paramObjectsList
	* @param keyId
	* @return
	*/
	private String getMulSearchCondition(Map<String, String> conditionMap,List<Object> paramObjectsList,String keyId){
		String keyValue = conditionMap.get(keyId);
		StringBuffer sqlBuff = new StringBuffer();
		if (SFHelper.isNotNull(keyValue)) {//防止有人输入特殊的字符，这两个
		if(keyValue.indexOf("'") >=0 || keyValue.indexOf("\"") >= 0){
		sqlBuff.append(" AND 1=2 ");
		return sqlBuff.toString();
		}
		if (keyValue.startsWith("%") || keyValue.endsWith("%") || keyValue.indexOf("%,%") > 0) {
		// 模糊查询：
		String replacement = "' or h1."+keyId+" like '";
		String paramValueTmp = " '" + keyValue.replaceAll(",", replacement) + "' ";
		sqlBuff.append(" AND (h1."+keyId+" like  "+paramValueTmp).append(") ");
		}else{//精确查询
		String paramValueTmp = "'" + keyValue.replaceAll(",", "','") + "'";
		sqlBuff.append(" AND h1."+keyId+" IN ( "+paramValueTmp).append(") ");
		}
		}
		return sqlBuff.toString();
		}

	/**
	* 获得From ... To ...查询条件
	*
	* @author ${bean.author}
	*
	* @date ${bean.dayTime}
	*
	* @param loginInfoObj
	* @param conditionMap
	* @param paramObjectsList
	* @param keyFrom
	* @param keyTo
	* @param keyId
	* @return
	*/
	private String getFromToSql(LoginInfoObj loginInfoObj, Map<String, String> conditionMap,List<Object> paramObjectsList,String keyFrom,String keyTo,String keyId){
		StringBuffer sqlBuff = new StringBuffer();

		String keyValue = conditionMap.get(keyFrom);
		if (SFHelper.isNotNull(keyValue)) {
		keyValue = keyValue.trim();
		//sqlBuff.append("AND h1.").append(keyId).append(" >= ? ");
		sqlBuff.append(" AND "+getFromTimeSql(loginInfoObj, "h1."+keyId, "?"));
		paramObjectsList.add(keyValue.trim().toUpperCase());
		}

		keyValue = conditionMap.get(keyTo);
		if (SFHelper.isNotNull(keyValue)) {
		keyValue = keyValue.trim();
		//sqlBuff.append("AND h1.").append(keyId).append(" <= ? ");
		sqlBuff.append(" AND "+getToTimeSql(loginInfoObj, "h1."+keyId, "?"));
		paramObjectsList.add(keyValue.trim().toUpperCase());
		}
		return sqlBuff.toString();
		}

	/**
	* 查询一条信息
	*
	* @author ${bean.author}
	* @date ${bean.dayTime}
	*
	* @param loginInfoObj
	* @param bizDataMap
	* @param otherReturnFields
	* @return
	*/
	public List<Map<String, Object>> getOne${bean.javaFlieName}Info(LoginInfoObj loginInfoObj, Map<String, String> conditionMap,
	String otherReturnFields) {
		// ==================================================================================
		// ==================================================================================
		// ==================================================================================
		Map<String, String> conditionMapTmp = new HashMap<String, String>();
		conditionMapTmp.putAll(conditionMap);// 浅复制一份副本，后续的操作在这个副本中进行。
		// key
		String pk02 = conditionMapTmp.get(${bean.javaFlieName}CVal.KEYNAME_${(bean.pk02)?upper_case});
		if (pk02 != null && pk02.trim().length() > 0) {
				pk02 = pk02.trim();
			} else {
				pk02 = "_NULL_";// 确保最多返回一条记录。
		}
		conditionMapTmp.put(${bean.javaFlieName}CVal.KEYNAME_${(bean.pk02)?upper_case}, pk02);
		// 查询符合输入的查询条件的主机信息管理 列表记录 // 上述查询条件确保最多返回一条记录。
		PageQueryDataSet rsDataList = get${bean.javaFlieName}sListByPage(loginInfoObj, conditionMapTmp, otherReturnFields);
		Map<String, Object> rsData = null;
		if (rsDataList != null && rsDataList.getBizDataList().size() > 0) {
		rsData = rsDataList.getBizDataList().get(0);
		} else {
		rsData = new HashMap<String, Object>();
		}
		List<Map<String, Object>> rsDataReturn = new ArrayList<Map<String, Object>>();
		rsDataReturn.add(rsData);
		return rsDataReturn;
		// ==================================================================================
		// ==================================================================================
	}

}
